I$-$Frame Inc. manufactures bamboo picture frames that sell for $$25$ each. Each frame requires $4$ feet of bamboo, which costs $$2\mathrm{.}00$ per foot. Each frame takes approximately $30$ minutes to build, and the labor rate averages $$14$ per hour. I$-$Frame has the following inventory policies:

Ending finished goods inventory should be $40\%$ of next month$$s sales.

Ending direct materials inventory should be $30\%$ of next month$$s production.

Expected unit sales $($frames$)$ for the upcoming months follow:

Expected Sales

March

April

May

June

July

August

Units

$330$

$360$

$410$

$510$

$485$

$535$

Variable manufacturing overhead is incurred at a rate of $$0\mathrm{.}40$ per unit produced. Annual fixed manufacturing overhead is estimated to be $$8,400($$$700$ per month$)$ for expected production of $4,000$ units for the year. The budgeted manufacturing cost per unit is $$17\mathrm{.}50.$ In addition, selling and administrative expenses are estimated at $$750$ per month plus $$0\mathrm{.}50$ per unit sold.

Of its sales, $80\%$ is in cash and $20\%$ is on credit. Of the credit sales, $50\%$ is collected during the same month of the sale, and $50\%$ is collected during the month following the sale.

Of direct materials purchases, $80\%$ is paid for during the month purchased and $20\%$ is paid in the following month. Direct materials purchases for March $1$ totaled $$2,800.$ All other operating costs are paid during the month incurred. Monthly fixed manufacturing overhead includes $$260$ in depreciation. During April, I$-$Frame plans to pay $$2,500$ for a piece of equipment.

I$-$Frame had $$13,500$ cash on hand on April $1.$ Assume the company can borrow interest$-$free in increments of $$1,000$ to maintain a $$13,000$ minimum ending cash balance. Repayments are made in the following month, assuming sufficient cash is available. $($For example, if you need $$4,300$ at the end of April to meet your desired ending cash balance, you would borrow $$5,000$ in April, and repay the $$5,000$ in May$).$

Required

Enter the given data into Excel $($start by setting up a $$Given Information$$ section and input the data that will be used to complete your budgets$).$ Then, complete the following $11$ budgets for the months of April, May and June, including a summary column for the $2$nd quarter. All budgets must be completed on the SAME sheet in Excel using proper formatting, formulas and cell references. If data$/$numbers come from the given information or from a previous budget, you must reference the cell as opposed to simply re$-$typing in the numbers. You should use formulas in Excel to perform all of your mathematical calculations. If you copy$/$paste budget numbers or manually type the numbers in$,$ you will not receive points. All data should be labeled and work should flow logically to be able to be followed by someone not involved in the creation of the document. This means that all calculations should be explicitly laid out so I can follow the process.

Sales Budget; $2.$ Production Budget; $3.$ Direct Materials Budget; $4.$ Direct Labor Budget; $5.$ Manufacturing Overhead Budget; $6.$ COGS Budget; $7.$ Selling, General & Administrative Expenses Budget; $8.$ Budgeted Income Statement; $9.$ Cash Receipts Budget; $10.$ Cash Payments Budget; $11.$ Summary Cash BudgetI$-$Frame Inc. manufactures bamboo picture frames that sell for $$25$ each. Each frame requires $4$ feet of bamboo, which costs $$2\mathrm{.}00$ per foot. Each frame takes approximately $30$ minutes to build, and the labor rate averages $$14$ per hour. I$-$Frame has the following inventory policies:

Ending finished goods inventory should be $40\%$ of next month$$s sales.

Ending direct materials inventory should be $30\%$ of next month$$s production.

Expected unit sales $($frames$)$ for the upcoming months follow:

Expected Sales

March

April

May

June

July

August

Units

$330$

$360$

$410$

$510$

$485$

$535$

Variable manufacturing overhead is incurred at a rate of $$0\mathrm{.}40$ per unit produced. Annual fixed manufacturing overhead is estimated to be $$8,400($$$700$ per month$)$ for expected production of $4,000$ units for the year. The budgeted manufacturing cost per unit is $$17\mathrm{.}50.$ In addition, selling and administrative expenses are estimated at $$750$ per month plus $$0\mathrm{.}50$ per unit sold.

Of its sales, $80\%$ is in cash and $20\%$ is on credit. Of the credit sales, $50\%$ is collected during the same month of the sale, and $50\%$ is collected during the month following the sale.

Of direct materials purchases, $80\%$ is paid for during the month purchased and $20\%$ is paid in the following month. Direct materials purchases for March $1$ totaled $$2,800.$ All other operating costs are paid during the month incurred. Monthly fixed manufacturing overhead includes $$260$ in depreciation. During April, I$-$Frame plans to pay $$2,500$ for a piece of equipment.

I$-$Frame had $$13,500$ cash on hand on April $1.$ Assume the company can borrow interest$-$free in increments of $$1,000$ to maintain a $$13,000$ minimum ending cash balance. Repayments are made in the following month, assuming sufficient cash is available. $($For example, if you need $$4,300$ at the end of April to meet your desired ending cash balance, you would borrow $$5,000$ in April, and repay the $$5,000$ in May$).$

Required

Enter the given data into Excel $($start by setting up a $$Given Information$$ section and input the data that will be used to complete your budgets$).$ Then, complete the following $11$ budgets for the months of April, May and June, including a summary column for the $2$nd quarter. All budgets must be completed on the SAME sheet in Excel using proper formatting, formulas and cell references. If data$/$numbers come from the given information or from a previous budget, you must reference the cell as opposed to simply re$-$typing in the numbers. You should use formulas in Excel to perform all of your mathematical calculations. If you copy$/$paste budget numbers or manually type the numbers in$,$ you will not receive points. All data should be labeled and work should flow logically to be able to be followed by someone not involved in the creation of the document. This means that all calculations should be explicitly laid out so I can follow the process.

Sales Budget; $2.$ Production Budget; $3.$ Direct Materials Budget; $4.$ Direct Labor Budget; $5.$ Manufacturing Overhead Budget; $6.$ COGS Budget; $7.$ Selling, General & Administrative Expenses Budget; $8.$ Budgeted Income Statement; $9.$ Cash Receipts Budget; $10.$ Cash Payments Budget; $11.$ Summary Cash Budget