Question

MBA-AC721 Project Part 2

Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel workbook and label it Wk 2 BRBS Budgeted Income Statement and create the 2018 budget using the following information. Be sure to use formulas. (Hint: add an extra sheet for computations then pull the totals into the Wk 2 income statement.)

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 (used to determine Cost of Goods Sold): Beginning inventory: $ 52,000 Purchases 4,160,000 Sales ? Ending Inventory $ 52,000 The goal is to keep 1,000 bikes in ending inventory to begin sales each new month. 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) 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

Answer 1

Sales Budget for the Year 2018	Online sales	Instore	Total
Volume	75000	5000	80000
Value [No of units * $104 pu]	$ 7,800,000.00	$    520,000.00	$ 8,320,000.00
Purchases	$ 4,160,000.00
Cost of Goods Sold Budget
Beginning Inventory	$      52,000.00
Add: Purchases	$ 4,160,000.00
Total goods available for sale	$ 4,212,000.00
Less: Ending Inventory	$      52,000.00
COGS	$ 4,160,000.00
Salaries Budget
Managerial staff:
Manager(1*45000*1.2)	$      54,000.00
Bookkeeper(1*40000*1.2)	$      48,000.00
Purch. & Recg. Supervisor(1*35000*1.2)	$      42,000.00
Other staff:
Warehouse(2.5FTEs*48 hrs.*$ 15/hr.*1.17)	$        2,106.00
Online sales staff(2 FTEs*48 hrs.*$ 15/hr*1.17)	$        1,685.00
Sales Clerks
(2 sc*5 days*5 hrs/day* $ 15/hr.*1.17)	$           878.00
(3 sc*5 days*3 hrs/day* $ 15/hr.*1.17)	$           790.00
4 sc*1 day*8 hrs* $ 15/hr.*1.17)	$           562.00
Instore Sales staff commn.(10%*520000)	$      52,000.00
Total budgeted salaries	$    202,020.00
Buy-Right Bike Store (BRBS)
Budgeted Income Statement
For the year ended Dec 31, 2018
Sales revenue		$ 8,320,000.00
Less: COGS		$ 4,160,000.00
Gross profit		$ 4,160,000.00
Less: Operating Expenses:
Salaries	$    202,020.00
Utility cost	$      13,200.00
Marketing Cost	$    175,000.00
Contributions & community service(10%*520000)	$      52,000.00
Other costs(incl.depn.& ins.)	$ 1,300,000.00
Total budgeted operating expenses		$ 1,742,220.00
Income before tax		$ 2,417,780.00
Less: Income tax at 29%		$    701,156.00
Net Revenue		$ 1,716,624.00