Get Instant Help From 5000+ Experts For

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Question:

You have agreed to do the following for Luke:

• Prepare the income statements using a spreadsheet program, with data he will provide. A chart must also be produced in the spreadsheet as detailed below.
• Prepare a short business report using a word processor program, with answers to specific questions as detailed below. Luke wants to use it to show the plan and his projections to his business partners and employees.

Write a paragraph explaining to Luke Rendalls how he can use your spreadsheet to do his own “what if” analyses or scenarios. Your explanation should be as clear as possible, in language suitable for someone who is not familiar with spreadsheets, to help them understand this feature of spreadsheeting.

Write another paragraph briefly explaining the “absolute referencing” feature – be sure to mention when it is used and why.

Introduction

The report aims to depict the changes in the income as per the various scenarios in Maxi’s Food Mart. The main changes have been considered with the variations in the net income as per the growth in sales, Mr. Rendall salary, hourly wage and hiring additional employees. The study has also compared the items for sales in 2017 by creating a pie chart compared to the different items held for sale in 2017.

Discussion
Worksheet A
 Maxi's Food Mart Pro Forma Income Statement Prepared on: February 1, 2018 2017 2018 2019 2020 Sales Deli \$240,000.00 \$257,400.00 \$277,348.50 \$300,229.75 Dairy \$720,000.00 \$772,200.00 \$832,045.50 \$900,689.25 Canned Goods \$480,000.00 \$514,800.00 \$554,697.00 \$600,459.50 Frozen Foods \$800,000.00 \$858,000.00 \$924,495.00 \$1,000,765.84 Meats \$840,000.00 \$900,900.00 \$970,719.75 \$1,050,804.13 Produce \$500,000.00 \$536,250.00 \$577,809.38 \$625,478.65 Dry Goods \$360,000.00 \$386,100.00 \$416,022.75 \$450,344.63 Gift Card \$60,000.00 \$64,350.00 \$69,337.13 \$75,057.44 Total Sales \$4,000,000.00 \$4,290,000.00 \$4,622,475.00 \$5,003,829.19 Cost of Goods Sold (CGS) Deli \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Dairy \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Canned Goods \$3,000,000.00 \$3,217,500.00 \$3,466,856.25 \$3,752,871.89 Frozen Foods \$2,600,000.00 \$2,788,500.00 \$3,004,608.75 \$3,252,488.97 Meats \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Produce \$2,600,000.00 \$2,788,500.00 \$3,004,608.75 \$3,252,488.97 Dry Goods \$2,640,000.00 \$2,831,400.00 \$3,050,833.50 \$3,302,527.26 Gift Card \$1,200,000.00 \$1,287,000.00 \$1,386,742.50 \$1,501,148.76 Total Cost of Goods Sold (CGS) \$18,040,000.00 \$19,347,900.00 \$20,847,362.25 \$22,567,269.64 Gross Profit \$14,040,000.00 \$15,057,900.00 \$16,224,887.25 \$17,563,440.45 Operating Expenses Sales & Marketing \$220,000.00 \$235,950.00 \$254,236.13 \$275,210.61 General & Administrative \$350,000.00 \$375,375.00 \$404,466.56 \$437,835.05 Depreciation \$20,000.00 \$20,000.00 \$20,000.00 \$20,000.00 Wages \$503,000.00 \$503,000.00 \$503,000.00 \$503,000.00 Common Costs \$2,106,000.00 \$2,258,685.00 \$2,433,733.09 \$2,634,516.07 Total Operating Expenses \$3,199,000.00 \$3,393,010.00 \$3,615,435.78 \$3,870,561.73 Gross Income (Income Before Taxes) \$10,841,000.00 \$11,664,890.00 \$12,609,451.48 \$13,692,878.72 Income Taxes \$3,794,350.00 \$4,082,711.50 \$4,413,308.02 \$4,792,507.55 Net Income \$7,046,650.00 \$7,582,178.50 \$8,196,143.46 \$8,900,371.17 Input Data: Assumptions and Additional Information Base Period Sales \$4,000,000.00 \$4,290,000.00 \$4,622,475.00 \$5,003,829.19 Store Items % of Sales CGS Rates Deli 6.00% 50.00% 2018 Growth 7.25% Dairy 18.00% 50.00% 2019 Growth 7.75% Canned Goods 12.00% 75.00% 2020 Growth 8.25% Frozen Foods 20.00% 65.00% Tax 35.00% Meats 21.00% 50.00% Produce 12.50% 65.00% Dry Goods 9.00% 66.00% Gift Card 1.50% 30.00% 100.00% Salaries Operating Expenses Luke 15.00% Sales and Marketing 5.50% Manager \$55,000.00 General and Administrative 8.75% Asst. Manager \$40,000.00 Depreciation Expense \$20,000.00 Total Working Hours in a year 2000 Wage \$12.00 No. of Employees 17
 Maxi's Food Mart Pro Forma Income Statement Prepared on: February 1, 2018 2017 2018 2019 2020 Sales Deli \$240,000.00 \$261,600.00 \$286,452.00 \$315,097.20 Dairy \$720,000.00 \$784,800.00 \$859,356.00 \$945,291.60 Canned Goods \$480,000.00 \$523,200.00 \$572,904.00 \$630,194.40 Frozen Foods \$800,000.00 \$872,000.00 \$954,840.00 \$1,050,324.00 Meats \$840,000.00 \$915,600.00 \$1,002,582.00 \$1,102,840.20 Produce \$500,000.00 \$545,000.00 \$596,775.00 \$656,452.50 Dry Goods \$360,000.00 \$392,400.00 \$429,678.00 \$472,645.80 Gift Card \$60,000.00 \$65,400.00 \$71,613.00 \$78,774.30 Total Sales \$4,000,000.00 \$4,360,000.00 \$4,774,200.00 \$5,251,620.00 Cost of Goods Sold (CGS) Deli \$2,000,000.00 \$2,180,000.00 \$2,387,100.00 \$2,625,810.00 Dairy \$2,000,000.00 \$2,180,000.00 \$2,387,100.00 \$2,625,810.00 Canned Goods \$3,000,000.00 \$3,270,000.00 \$3,580,650.00 \$3,938,715.00 Frozen Foods \$2,600,000.00 \$2,834,000.00 \$3,103,230.00 \$3,413,553.00 Meats \$2,000,000.00 \$2,180,000.00 \$2,387,100.00 \$2,625,810.00 Produce \$2,600,000.00 \$2,834,000.00 \$3,103,230.00 \$3,413,553.00 Dry Goods \$2,640,000.00 \$2,877,600.00 \$3,150,972.00 \$3,466,069.20 Gift Card \$1,200,000.00 \$1,308,000.00 \$1,432,260.00 \$1,575,486.00 Total Cost of Goods Sold (CGS) \$18,040,000.00 \$19,663,600.00 \$21,531,642.00 \$23,684,806.20 Gross Profit \$14,040,000.00 \$15,303,600.00 \$16,757,442.00 \$18,433,186.20 Operating Expenses Sales & Marketing \$220,000.00 \$239,800.00 \$262,581.00 \$288,839.10 General & Administrative \$350,000.00 \$381,500.00 \$417,742.50 \$459,516.75 Depreciation \$20,000.00 \$20,000.00 \$20,000.00 \$20,000.00 Wages \$503,000.00 \$503,000.00 \$503,000.00 \$503,000.00 Common Costs \$2,106,000.00 \$2,295,540.00 \$2,513,616.30 \$2,764,977.93 Total Operating Expenses \$3,199,000.00 \$3,439,840.00 \$3,716,939.80 \$4,036,333.78 Gross Income (Income Before Taxes) \$10,841,000.00 \$11,863,760.00 \$13,040,502.20 \$14,396,852.42 Income Taxes \$3,794,350.00 \$4,152,316.00 \$4,564,175.77 \$5,038,898.35 Net Income \$7,046,650.00 \$7,711,444.00 \$8,476,326.43 \$9,357,954.07 Input Data: Assumptions and Additional Information Base Period Sales \$4,360,000.00 \$4,774,200.00 \$5,251,620.00 Store Items CGS Rates Deli 50.00% 2018 Growth 9.00% Dairy 50.00% 2019 Growth 9.50% Canned Goods 75.00% 2020 Growth 10.00% Frozen Foods 65.00% Tax 35.00% Meats 50.00% Produce 65.00% Dry Goods 66.00% Gift Card 30.00% Salaries Operating Expenses Luke 15.00% Sales and Marketing Manager \$55,000.00 General and Administrative Asst. Manager \$40,000.00 Depreciation Expense Total Working Hours in a year 2000 Wage No. of Employees 17
 Maxi's Food Mart Pro Forma Income Statement Prepared on: ############## 2017 2018 2019 2020 Sales Deli \$240,000.00 \$249,600.00 \$262,080.00 \$276,494.40 Dairy \$720,000.00 \$748,800.00 \$786,240.00 \$829,483.20 Canned Goods \$480,000.00 \$499,200.00 \$524,160.00 \$552,988.80 Frozen Foods \$800,000.00 \$832,000.00 \$873,600.00 \$921,648.00 Meats \$840,000.00 \$873,600.00 \$917,280.00 \$967,730.40 Produce \$500,000.00 \$520,000.00 \$546,000.00 \$576,030.00 Dry Goods \$360,000.00 \$374,400.00 \$393,120.00 \$414,741.60 Gift Card \$60,000.00 \$62,400.00 \$65,520.00 \$69,123.60 Total Sales \$4,000,000.00 \$4,160,000.00 \$4,368,000.00 \$4,608,240.00 Cost of Goods Sold (CGS) Deli \$2,000,000.00 \$2,080,000.00 \$2,184,000.00 \$2,304,120.00 Dairy \$2,000,000.00 \$2,080,000.00 \$2,184,000.00 \$2,304,120.00 Canned Goods \$3,000,000.00 \$3,120,000.00 \$3,276,000.00 \$3,456,180.00 Frozen Foods \$2,600,000.00 \$2,704,000.00 \$2,839,200.00 \$2,995,356.00 Meats \$2,000,000.00 \$2,080,000.00 \$2,184,000.00 \$2,304,120.00 Produce \$2,600,000.00 \$2,704,000.00 \$2,839,200.00 \$2,995,356.00 Dry Goods \$2,640,000.00 \$2,745,600.00 \$2,882,880.00 \$3,041,438.40 Gift Card \$1,200,000.00 \$1,248,000.00 \$1,310,400.00 \$1,382,472.00 Total Cost of Goods Sold (CGS) \$18,040,000.00 \$18,761,600.00 \$19,699,680.00 ############# Gross Profit \$14,040,000.00 \$14,601,600.00 \$15,331,680.00 ############# Operating Expenses Sales & Marketing \$220,000.00 \$228,800.00 \$240,240.00 \$253,453.20 General & Administrative \$350,000.00 \$364,000.00 \$382,200.00 \$403,221.00 Depreciation \$20,000.00 \$20,000.00 \$20,000.00 \$20,000.00 Wages \$503,000.00 \$503,000.00 \$503,000.00 \$503,000.00 Common Costs \$2,106,000.00 \$2,190,240.00 \$2,299,752.00 \$2,426,238.36 Total Operating Expenses \$3,199,000.00 \$3,306,040.00 \$3,445,192.00 \$3,605,912.56 Gross Income (Income Before Taxes) \$10,841,000.00 \$11,295,560.00 \$11,886,488.00 ############# Income Taxes \$3,794,350.00 \$3,953,446.00 \$4,160,270.80 \$4,399,153.44 Net Income \$7,046,650.00 \$7,342,114.00 \$7,726,217.20 \$8,169,856.40 Input Data: Assumptions and Additional Information Base Period Sales \$4,000,000.00 \$4,160,000.00 \$4,368,000.00 \$4,608,240.00 Store Items % of Sales CGS Rates Deli 6.00% 50.00% 2018 Growth 4.00% Dairy 18.00% 50.00% 2019 Growth 5.00% Canned Goods 12.00% 75.00% 2020 Growth 5.50% Frozen Foods 20.00% 65.00% Tax 35.00% Meats 21.00% 50.00% Produce 12.50% 65.00% Dry Goods 9.00% 66.00% Gift Card 1.50% 30.00% 100.00% Salaries Operating Expenses Luke 15.00% Sales and Marketing 5.50% Manager \$55,000.00 General and Administrative 8.75% Asst. Manager \$40,000.00 Depreciation Expense \$20,000.00 Total Working Hours in a year 2000 Wage \$12.00 No. of Employees 17
 Maxi's Food Mart Pro Forma Income Statement Prepared on: February 1, 2018 2017 2018 2019 2020 Deli \$240,000.00 \$257,400.00 \$277,348.50 \$300,229.75 Dairy \$720,000.00 \$772,200.00 \$832,045.50 \$900,689.25 Canned Goods \$480,000.00 \$514,800.00 \$554,697.00 \$600,459.50 Frozen Foods \$800,000.00 \$858,000.00 \$924,495.00 \$1,000,765.84 Meats \$840,000.00 \$900,900.00 \$970,719.75 \$1,050,804.13 Produce \$500,000.00 \$536,250.00 \$577,809.38 \$625,478.65 Dry Goods \$360,000.00 \$386,100.00 \$416,022.75 \$450,344.63 Gift Card \$60,000.00 \$64,350.00 \$69,337.13 \$75,057.44 Total Sales \$4,000,000.00 \$4,290,000.00 \$4,622,475.00 \$5,003,829.19 Cost of Goods Sold (CGS) Deli \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Dairy \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Canned Goods \$3,000,000.00 \$3,217,500.00 \$3,466,856.25 \$3,752,871.89 Frozen Foods \$2,600,000.00 \$2,788,500.00 \$3,004,608.75 \$3,252,488.97 Meats \$2,000,000.00 \$2,145,000.00 \$2,311,237.50 \$2,501,914.59 Produce \$2,600,000.00 \$2,788,500.00 \$3,004,608.75 \$3,252,488.97 Dry Goods \$2,640,000.00 \$2,831,400.00 \$3,050,833.50 \$3,302,527.26 Gift Card \$1,200,000.00 \$1,287,000.00 \$1,386,742.50 \$1,501,148.76 Total Cost of Goods Sold (CGS) \$18,040,000.00 \$19,347,900.00 \$20,847,362.25 \$22,567,269.64 Gross Profit \$14,040,000.00 \$15,057,900.00 \$16,224,887.25 \$17,563,440.45 Operating Expenses Sales & Marketing \$220,000.00 \$235,950.00 \$254,236.13 \$275,210.61 General & Administrative \$350,000.00 \$375,375.00 \$404,466.56 \$437,835.05 Depreciation \$20,000.00 \$20,000.00 \$20,000.00 \$20,000.00 Wages \$571,000.00 \$571,000.00 \$571,000.00 \$571,000.00 Common Costs \$1,123,200.00 \$1,204,632.00 \$1,297,990.98 \$1,405,075.24 Total Operating Expenses \$2,284,200.00 \$2,406,957.00 \$2,547,693.67 \$2,709,120.90 Gross Income (Income Before Taxes) \$11,755,800.00 \$12,650,943.00 \$13,677,193.58 \$14,854,319.55 Income Taxes \$4,114,530.00 \$4,427,830.05 \$4,787,017.75 \$5,199,011.84 Net Income \$7,641,270.00 \$8,223,112.95 \$8,890,175.83 \$9,655,307.71 Input Data: Assumptions and Additional Information Base Period Sales \$4,000,000.00 \$4,290,000.00 \$4,622,475.00 \$5,003,829.19 Store Items % of Sales CGS Rates Deli 6.00% 50.00% 2018 Growth 7.25% Dairy 18.00% 50.00% 2019 Growth 7.75% Canned Goods 12.00% 75.00% 2020 Growth 8.25% Frozen Foods 20.00% 65.00% Tax 35.00% Meats 21.00% 50.00% Produce 12.50% 65.00% Dry Goods 9.00% 66.00% Gift Card 1.50% 30.00% 100.00% Salaries Operating Expenses Luke 8.00% Sales and Marketing 5.50% Manager \$55,000.00 General and Administrative 8.75% Asst. Manager \$40,000.00 Depreciation Expense \$20,000.00 Total Working Hours in a year 2000 Wage \$14.00 No. of Employees 17
 Input Data: Assumptions and Additional Information Base Period Sales \$4,000,000.00 \$4,290,000.00 \$4,622,475.00 \$5,003,829.19 Store Items % of Sales CGS Rates Deli 6.00% 50.00% 2018 Growth 7.25% Dairy 18.00% 50.00% 2019 Growth 7.75% Canned Goods 12.00% 75.00% 2020 Growth 8.25% Frozen Foods 20.00% 65.00% Tax 35.00% Meats 21.00% 50.00% Produce 12.50% 65.00% Dry Goods 9.00% 66.00% Gift Card 1.50% 30.00% 100.00% Salaries Operating Expenses Luke 15.00% Sales and Marketing 5.50% Manager \$55,000.00 General and Administrative 8.75% Asst. Manager \$40,000.00 Depreciation Expense \$20,000.00 Total Working Hours in a year 2000 Wage \$14.00 No. of Employees 19

In the given case Luke Rendall may use the what if analysis to depict the changes in the salaries as per gross profit in different situation. The what if analysis may be used in case the total cost of goods sold is higher than the revenue generated from. If the total cost of goods sold is greater than total sales and the company incurs a loss then the what of analysis will be automatically trace this and generate the result as not applicable. The what if analysis may be beneficial to trace the different types of the results based on the changes in other variables as well. For instance, in the company does not need to any depreciation for a year then it can trace it from beforehand. The What-if analysis in excel may be used in excel for setting the values in one or more formulas to explore the different set of results. In case of building two analysis Luke Rendall ay use “several different sets of values in one or more formulas to explore all the various results” (Rodgers 2016).

This analysis when used in building two budgets may assume specific level of revenue and determine the values which will be produced in excel. “Excel provides several types of the different tools to help a company perform the analysis that fits appropriately to the business needs” (Poatsy et al.2016).

There are different situations which calls for the needs to implement absolute cell referencing. It has been seen that unlike relative references, absolute references do not get altered when copied or filled. The use of this method may be used to keep a row “and/or column constant”. Absolute reference is designed with the addition to a dollar sign (\$). This may be used by preceding the column reference, roe reference or both. The use of \$A\$ is used when the row and column do not change on copying the data (Winston 2016). The use of A\$2 is used when the row does not change when copied. The use of \$A2 column does not change when copied. The use of absolute cell referencing is appropriate when a specific set of data used needs to be fixed as per the required solution. The absolute cell reference use helps in minimizing the overall time required for copying the data into other cells, hence it saves time by fixing a cell and making the changes in other relevant cells in excel (Liengme 2015).

Recommendation

The main depictions relating to the changes have suggested that there is a positive relation between sales and net income.  This has been understood with the fact that when the sales have increased with higher percentage the net income has also increased accordingly. The hiring of additional employees has also demonstrated an increasing in the operating expenses.

References

Liengme, B., 2015. A guide to Microsoft Excel 2013 for scientists and engineers. Academic Press.

Poatsy, M.A., Mulbery, K., Davidson, J. and Grauer, R., 2016. Exploring Microsoft Office Excel 2016 Comprehensive. Pearso

Rodgers, K.A., 2016. Correlation Analysis with Excel Handout.

Winston, W., 2016. Microsoft Excel data analysis and business modeling. Microsoft press.

Cite This Work

"Maxis Food Mart Pro Forma Income Statement." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1.

My Assignment Help (2020) Maxis Food Mart Pro Forma Income Statement [Online]. Available from: https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1
[Accessed 14 September 2024].

My Assignment Help. 'Maxis Food Mart Pro Forma Income Statement' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1> accessed 14 September 2024.

My Assignment Help. Maxis Food Mart Pro Forma Income Statement [Internet]. My Assignment Help. 2020 [cited 14 September 2024]. Available from: https://myassignmenthelp.com/free-samples/cois11011-foundations-of-business-computing1.

Get instant help from 5000+ experts for

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost