MBAAC721 Project Part 2 Create the 2018 budget for BuyRight

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

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