Frontline Airline accounting budgets

Instructions:

Frontier Airlines Assembly manufactures seats for private planes. They need to budget for the month of October, 2020. They want to know if they will have enough funds for a $3,000,000 planned expansion. Your job is to complete the budgets indicated and answer the questions below.

The CEO has said she wants to build & sell 3000 seats in October, 4000 seats in November, and 2500 seats in December. Each seat will sell for $4500 each. The cost of the materials for each seat has been broken down: Leather $400, Frame & Cushion $600, Misc. Bolts $40. The production manager says it takes about 25 hours to build and average labor cost is $35/hour per seat.

Other costs you may want to consider include utilities for the factory $20/seat, factory salaries for janitor & supervisor $10,000/month, Depreciation on factory equipment $15,000/month, CEO Salary is $240,000/year, CFO Salary is $180,000/year, Admin Assistant is $35,000/year. Selling expenses are $25,000/month. Misc. office expenses are $1000/month, Office Equipment depreciation $2,500/month. Everything is paid in the month of, as the CEO doesn’t like to owe anyone.

The CEO would like 30% of next month’s production ready to sell so there is no shortage.

Cash is collected 60% in the month of sale, and the remainder in the month following. Beginning Balances are listed below:

Accounts Receivable on 10/1 is $50,000

Accounts Payable on 10/1 is $0

Retained Earnings on 10/1 is $1,600,000

Cash 10/1 is $250,000

Required Profit Margin 25%

Income Tax Rate is 20%

Finished Goods, 10/1 is $20,000

Finished Goods, 10/31 is $45,000

WIP, 10/1 is $10,000

WIP, 10/31 is $15,000

Materials beginning, 10/1 is $40,000 (Leather $30,000; Frames $8,000; Misc. Supplies $2,000)

Materials desired ending, 10/31 is $8,000 (Leather $5,000; Frames $2,000; Misc. Supplies $1,000)

Answer the following questions in a text box:

What was the 10/31 balance in Accounts Receivable?

Is there enough cash on October 31 to move forward with a $3,000,000 expansion, if $1,000,000 must be set aside for operational liquidity? Why or why not?

What is the profit margin? Does it meet the CEO’s minimum requirement?

Prepare:

Prepare a Sales Budget, Production Budget, Direct Materials Budget, Direct Labor Budget, Factory Overhead Budget, Cost of Goods Sold, Selling & Admin Expense Budget, Proforma Income Statement, Cash Receipts Budget, Cash Payments Budget, Cash Budget. Use formulas and cell references when using Excel. (NOTE: not all information is needed)