Marys Mountain Ski shop is planning for spring 2021 and needs help with the budget for April, May and June. These are her three busiest months, so she needs to get this right. Here is a copy of the budgeted balance sheet for March, the end of their last budget cycle.

Budget Assignment

Proforma Balance Sheet
31-Mar-21
Assets
Cash		$5,000
Supplies		100
Inventory		778,000
Leasehold Improvements	41,760
Accumulated Depreciation	6,380	35,380
Total Assets		818,480
Liabilities & Equity
Liabilities
Bank Loan Payable		12,000
Total Liabilities		12,000
Equity
Common Stock		20,000
Retained Earnings		786,480
Total Equity		806,480
Total Liabilities & Equity		$818,480

Marry sells mountain bikes for an average price of $3,500 each and kids bikes for an average price of $150 each. She buys the mountain bikes for $2,800 each and kids bikes for $80. Here are the anticipated number of bikes she plans to sell for the second quarter.

	April	May	June
# of mountain bikes	120	125	90
# of kids bikes	35	40	75

Note: Marry has no credit customers

She knows that there are enough bikes in stock to cover sales for April and May but will buy more inventory at the beginning of June to maintain a good inventory. She expects to spend $568,000. 70% of these inventory purchases will be on credit and will be paid in July.

Rent of $8,000 is due to be paid at the first of each the month and employees are paid at the end of each month. Marys regular payroll is $10,000 each month but in May, she will also pay her cousin $1,500 to help out on weekends.

The leasehold improvements are expected to last 6 years and will then be worthless. Jane uses approximately $100 of supplies per month. She will buy $400 of supplies in May.

Marry needed to take out a$12,000 loan at the end of March (March 31). The banks interest rate is 5%. She will pay the loan and all the interest in September.

Every year, the Chamber of Commerce buys a bike for $3,000 to be raffled off in July. Marry expects to be paid in June but will deliver the bike to the lucky winner in July.

Required: In excel, create an operating budget (budgeted income statement) and a cash budget for April, May and June. Also make a budgeted balance sheet for June 30, 2021.

Follow the following format:

Note, I know this assignment will be challenging and I encourage you to collaborate with your friends. Collaboration is when all people participate equally to solve a problem. Do not copy the work of others. I will know and you put your friends in an uncomfortable and dangerous position. A 5% assignment is not worth that risk as I will report all cases to Student affairs, and it will go on your permanent record.

Using Excel, follow this format (you dont need to use these colours): Make Sure to link all cells

Pro Forma Balance Sheet 30-Jun-21 Assets Hint: You should have 4 assets and a contra asset. There are 4 liabilities Total Assets Liabilities & Equity Liabilities Total Liabilities Equity Total Equity Total Liabilities & Equity | Check Figures: Total budgeted Net Income for the 3 months Ended June 30, 2021 $187,310 Projected Cash at the end of June $ 976,700 Total Assets $1,406,540 April May June Total Sales Cost of Goods Sold Gross Profit Hint: You should have 5 operating expenses Total Operating expense Net Income Cash Budget April May June Beginning Cash Total Cash Available Cash Disbusements Total Cash Disbursements Ending Cash Pro Forma Balance Sheet 30-Jun-21 Assets Hint: You should have 4 assets and a contra asset. There are 4 liabilities Total Assets Liabilities & Equity Liabilities Total Liabilities Equity Total Equity Total Liabilities & Equity | Check Figures: Total budgeted Net Income for the 3 months Ended June 30, 2021 $187,310 Projected Cash at the end of June $ 976,700 Total Assets $1,406,540 April May June Total Sales Cost of Goods Sold Gross Profit Hint: You should have 5 operating expenses Total Operating expense Net Income Cash Budget April May June Beginning Cash Total Cash Available Cash Disbusements Total Cash Disbursements Ending Cash