Question

In: Accounting

MBA-AC721 Project Part 2 Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet...

MBA-AC721 Project Part 2 Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel workbook for BRBC Budgeted Income Statement and create the 2018 budget using the following information.

Budgeted Sales of Bike C: Online 75,000 bikes; instore 5,000 bikes. Bike C is purchased from Built-Right Bike Company, a sister company in the Biltmore Bicycle Corporation (BMBC), for $52 and is sold for $104.

Inventory: Beginning inventory: $ 52,000

Purchases 4,108,000

Sales ?

Ending Inventory $ 52,000

Employees:

Managerial Staff: Manager: 1 FTE, $45,000 annual salary plus 20% benefits

Bookkeeper: 1 FTE, $40,000 annual salary plus 20% benefits

Purchasing & Receiving Supervisor: 1 FTE, $35,000 annual salary plus 20% benefits Other staff: Warehouse: 2.5 FTEs make $15 per hour plus 17% benefits Online sales staff: 2 FTEs, $15 per hour plus 17% benefits (no commission)

NOTE: 1 FTE = 2080 hours

Store Hours: Monday – Saturday 10:00 am – 6:00 pm

2 sales clerks work from 10-3 M-F

3 sales clerks work from 3-6 M-F

4 sales clerks work 10-6 on Saturdays

Sales clerks earn $15 per hour plus 17% benefits Instore sales staff share commission equal to 10% of instore sales.

Budgeted Utility cost: $13,200

Budgeted Marketing Cost: $175,000

Contributions & community service: 10% of instore revenue

Other costs (includes depreciation, insurance, etc): $1,300,000

Income tax 29% of Net Revenue

Expert Solution

Budgeted Income statement

Budgeted Sales Instore 5,000 Bikes

Online 75,000 Bikes

Total Budgeted sales 80,000 Bikes

Budgeted Sale price /Unit $104

Total Budgeted sales (in $) ($104*80,000) =$83,20,000

Budgeted Purchase Price/Unit $52

Total Budgeted Purcahse Price ($52*80,000)= $41,60,000

Net Contribution ($83,20,000- $41,60,000)= $41,60,000

Other Cost

Managerial Staff Salary ($45,000+20% of $45000(i.e. $9,000)= $54,000

Bookekeeper Salary($40,000 +20% of $40,000(i.e. $8,000) =$48,000

Purachsing and receivingSupervisor salary= ($35,000 +20%of $35,000i.e.($7,000)=$42,000

Other Staff Warehouse Salary (2.5 * 2080* $15) =$78,000

Other benefits for Staff Warehouse (17% of $ 78,000)= $13,260

Online Sales Staff (2 * 2080 *$15) =$ 62,400

Benefits for Online sales staff (17% of $ 62,400)= $10,608

Sales Clerk Salary

(1FTE=2080 HOURS ,OFFICE TIME=10A.M. TO 6P.M. MEANS 8 HRS A DAY.HENCE TOTAL WORKING DAY =2080/8=260DAYS

2 SALES CLERK * 5 HRS/DAY * $ 15/HR * 260 days =$ 39,000

3 SALES CLERK *5HRS/DAY * $15/HR * 260 DAYS= $ 58,500

4 SALES CLERK *8HRS/DAY *$ 15/HR *52 SATURDAYS = $24,960

BENEFITS FOR INSTORE SALES STAFF 17% OF( $39,000+$58,500+$24,960)=$1,22,460

INSTORE SALES STAFF COMMISSION = $52,000

(Instore Sales =5000 Units at $ 104=52,00,000 * 10%)

Budgeted Utility Cost =$ 13,200

Budg.marketing Cost = $1,75,000

Other Cost =$13,00,000

Contribution and Community Service (10% of Instore revenue)= $52,000

Hence Total of Other Cost other than Purcahse price= 54,000+ 48000+42000+78,000+13260+62400+10608+39000+58500+24960+122460+52000+13200+175000+1300000+52000= 21,45,388

Net Revenue= Net Contribution-Other Cost

=4160000-2145388=2014612

Income tax Provision(29% of 2014612)=584237.48

Budgeted Profit after tax = ( Net Revenue -tax)=2014612-584237.48=$1430374.52

ekkarill92 answered 2 years ago

Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel...

Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel workbook for BRBC Budgeted Income Statement and create the 2018 budget using the following information. Budgeted Sales of Bike C: Online 75,000 bikes; instore 5,000 bikes. Bike C is purchased from Built-Right Bike Company, a sister company in the Biltmore Bicycle Corporation (BMBC), for $52 and is sold for $104. Inventory: Beginning inventory: $ 52,000 Purchases 4,108,000 Sales ? Ending Inventory ...

Please provide a step by step solution create a Vacation Budget worksheet Add at least 6...

Please provide a step by step solution create a Vacation Budget worksheet Add at least 6 other expenses related to your planned vacation (car rental etc.) enter an estimate of the cost of for each item Use a function to calculate the total estimated costs. g) Enter a function to calculate the average cost per day. h) Create a chart (you choose the type) in this same worksheet based on this trip to display the estimated costs. Add a title...

I need recommendations, comments for project proposal of my MBA Capstone project, is it right topic...

I need recommendations, comments for project proposal of my MBA Capstone project, is it right topic which i chose, description, problem to be addressed? I begin my graduate capstone project i need just opinion of experts if I am on the right way. This was requirements from my professor: Week 1: Project Proposal Describe in 2-3 sentences (a short paragraph) the project proposal that contains the following: 1.Introduction and description of your selected business environment 2.Problem to be Addressed And this...

Please complete it in C++ Part 2: Queue Create a New Project and give your project...

Please complete it in C++ Part 2: Queue Create a New Project and give your project a name, say Lab6b. Download the given source files StackArr.h, StackArr.cpp, QueueArr.h and QueueArr.cpp from Canvas and save them to your Lab6b folder. Also import them to your project. Add a source file to your project, called QueueMain.cpp and implement your program according to the following: prompt the user to input a string; change each uppercase letter to lowercase; place each letter both in...

Create an excel worksheet and populate it, in columns, with values of X that are -2,...

Create an excel worksheet and populate it, in columns, with values of X that are -2, -1, 0, 1, 2 and values of Y that are -1, 1,1,1,3. As an aside, note that the sample mean of X is 0. Create the columns shown below, and enter the proper formulas into excel. (X minus (X minus mean) Mean) times (Y Y Y-mean X X-mean squared minus mean) Y-hat u-hat -1 -2 1 -1 1 0 1 1 3 2 sum...

Step 1: Create a new Java project named ContainerPartyProject --------- Step 2: Add a ContainerParty class...

Step 1: Create a new Java project named ContainerPartyProject --------- Step 2: Add a ContainerParty class to your project. It should contain: - The date of the party - A list of people attending - A list of containers at the party - The address of the party (this can be either a String or a Class) --------- Step 3: Add a Container class in your project. It should be an abstract class. Your Container class...

When preparing a budget to buy a new home, you should add an additional ____ percent...

When preparing a budget to buy a new home, you should add an additional ____ percent to the monthly payment on the loan for such things as homeowner's insurance, property taxes, maintenance, and upkeep.

Week 5 Project Built Right Bike Company (BRBC) is an established manufacturer of quality bicycles. They...

Week 5 Project Built Right Bike Company (BRBC) is an established manufacturer of quality bicycles. They manufacture three styles of bicycles. Bicycle A is a popular racing bicycle primarily sold to dealers in the bicycle racing circuit. Their material is lightweight and durable with detachable joints for easy disassembly and storage. This market has been declining over the past couple of years. Bicycle B is a sturdy leisure bike typically sold to resorts for use by vacationers. This market is...

Create a project charter for opening a Target store in Canada.Include the purpose of the...

Create a project charter for opening a Target store in Canada. Include the purpose of the project, any assumptions/constraints, scope description, and any milestones the project would incur.

Adia Davis Principles of Accounting 2: Summer 2018 - Accounting 206 Master Budget Project instructions |...

Adia Davis Principles of Accounting 2: Summer 2018 - Accounting 206 Master Budget Project instructions | help [The following information applies to the questions displayed below.] Near the end of 2015, the management of Dimsdale Sports Co., a merchandising company, prepared the following estimated balance sheet for December 31, 2015. DIMSDALE SPORTS COMPANY Estimated Balance Sheet December 31, 2015 Assets Cash $ 35,000 Accounts receivable 520,000 Inventory 95,000 Total current assets $ 650,000 Equipment $ 576,000 Less accumulated...