Question

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)

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

BRBC Budgeted Income Statement
Sales:
Online (75,000*104)	7,800,000
Instore (5,000*104)	520,000	8,320,000
Cost of Goods Sold (80,000*52)		(4,160,000)
Managerial Staff Salary:
Manager Salary	(45000)
Manager Benefits (20% of Salary)	(9,000)	(54,000)
Bookkeeper Salary	(40,000)
Bookkeeper Benefits (20% of Salary)	(8,000)	(48,000)
Purchasing & Receiving Supervisor Salary	(35,000)
Purchasing & Receiving Supervisor Benefits (20% of Salary)	(7,000)	(42,000)
Other Staff Salary:
Warehouse Staff Salary (2.5FTE*2080 Hours *15)	(78,000)
Warehouse Staff Benefits (2.5 FTE*2080 Hours *15)	(13,260)	(91,260)
Online Sales Staff Salary (2 FTE*2080 Hours *15)	(62,400)
Online Sales Staff Benefits (17% of Salary)	(10,608)	73,008
Sales Clerk Salary:
2 Staff * 5 Hours Daily * 5 Days * 52 Weeks * 15 Per Hour	(39,000)
3 Staff * 3 Hours Daily * 5 Days * 52 Weeks * 15 Per Hour	(35,100)
4 Staff * 8 Hours * 1 Day * 52 Weeks * 15 Per Hour	(24,960)
Benefits (17% of (39,000+35,100+24,960))	(16,840)
Commission (10% of Instore Sales)	(52,000)	(167,900)
Budgeted Utility Cost		(13,200)
Budgeted Marketing Cost		(175,000)
Contribution & community service (10% of Instore Sales)		(52,000)
Other costs (includes depreciation, insurance, etc.)		(1,300,000)
Net Revenue		2,143,632
Income Tax (29% of Net Revenue)		621,653
Net Profit		1,521,979

FTE are assumed to be paid for 2,080 hours per year.

Cost of Goods Sold is taken only for the quantity sold i.e. 80,000 bikes (including Instore as well as online sales).