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	7800000	520000	8320000

Purchases

4160000

Cost of Goods Sold Budget
Beginning Inventory	52000
Add: Purchases	4160000
Total goods available for sale	4212000
Less: Ending Inventory	52000
COGS	4160000

Salaries Budget
Managerial staff:
Manager(1*45000*1.2)	54000
Bookkeeper(1*40000*1.2)	48000
Purch. & Recg. Supervisor(1*35000*1.2)	42000
Other staff:
Warehouse(2.5FTEs*48 hrs.*52 wks*$ 15/hr.*1.17)	109512
Online sales staff(2 FTEs*48 hrs.*52 wks*$ 15/hr*1.17)	87610
Sales Clerks
(2 sc*5 days*52 wks.*5 hrs/day* $ 15/hr.*1.17)	45630
(3 sc*5 days*52 wks.*3 hrs/day* $ 15/hr.*1.17)	41067
4 sc*1 day*52 wks.*8 hrs* $ 15/hr.*1.17)	29203
Instore Sales staff commn.(10%*520000)	52000
Total budgeted salaries	509022

Buy-Right Bike Store (BRBS)
Budgeted Income Statement
For the year ended Dec 31, 2018
Sales revenue		8320000
Less: COGS		4160000
Gross profit		4160000
Less: Operating Expenses:
Salaries	509022
Utility cost	13200
Marketing Cost	175000
Contributions & community service(10%*520000)	52000
Other costs(incl.depn.& ins.)	1300000
Total budgeted operating expenses		2049222
Income before tax		2110778
Less: Income tax at 29%		612126
Net Revenue		1498653